﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using McTools.Xrm.Connection;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Client;
using OfficeOpenXml;
using CRMHelpers;
using Microsoft.Crm.Sdk.Messages;
using System.IO;
using Microsoft.Xrm.Sdk.Metadata;
using System.Collections;
using WaitingBox;
using Microsoft.Xrm.Sdk.Query;
using OfficeOpenXml.Style;
using System.Threading;
using System.Globalization;
using Microsoft.Xrm.Sdk.Messages;





namespace ExcelToDynamicsCRM
{
    public partial class MainForm : Form
    {
        #region Variables

        /// <summary>
        /// Connection Manager for Dynamics CRM 2011
        /// </summary>
        ConnectionManager cManager;

        /// <summary>
        /// Connection Statusbar for Dynamics CRM 2011
        /// </summary>
        CrmConnectionStatusBar ccsb;

        /// <summary>
        /// Crm organization service
        /// </summary>
        IOrganizationService service;

        CrmOrganizationServiceContext crmContext;

        ExcelPackage pck;

        ExcelPackage pckbackup;
     
        LabelHelper lbhelp;
        EntityHelper ethelp;
        String displayDebuglog = "";
        String filelog = "";
        Int32 _defaultLanguage = 1033;
        String crmurl;
        String Filename;
        Hashtable EntityMetadataAttributesListinExcelPackage = new Hashtable();
        Hashtable EntityMetadataRelationShipListinExcelPackage = new Hashtable();
        Hashtable EntityColumnsListinExcelPackage = new Hashtable();
    
        

        #endregion Variables

        #region Constructors
        public MainForm()
        {
            InitializeComponent();
            try
            {

                if (System.Configuration.ConfigurationManager.AppSettings["displayDebuglog"] != null)
                    displayDebuglog = System.Configuration.ConfigurationManager.AppSettings["displayDebuglog"];
                if (System.Configuration.ConfigurationManager.AppSettings["filelog"] != null)
                    filelog = System.Configuration.ConfigurationManager.AppSettings["filelog"];
               // if (System.Configuration.ConfigurationManager.AppSettings["defaultlanguage"] != null)
                 //   _defaultLanguage = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["defaultlanguage"]);

                this.Text += " (" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + ")";
                // Instanciates the connection manager
                this.cManager = new ConnectionManager(this);
                this.cManager.ConnectionSucceed += new ConnectionManager.ConnectionSucceedEventHandler(cManager_ConnectionSucceed);
                this.cManager.ConnectionFailed += new ConnectionManager.ConnectionFailedEventHandler(cManager_ConnectionFailed);
                this.cManager.StepChanged += new ConnectionManager.StepChangedEventHandler(cManager_StepChanged);

                // Instanciates the connection status bar
                this.ccsb = new CrmConnectionStatusBar(this.cManager);
                this.Controls.Add(ccsb);
                //progressBar1.MarqueeAnimationSpeed = 30;

                

            }

            catch (Exception error)
            {
                MessageBox.Show(this, "Error while intializing application: " + error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        #endregion Constructors

        #region Connection Handlers

        void cManager_StepChanged(object sender, StepChangedEventArgs e)
        {
            this.ccsb.SetMessage(e.CurrentStep);
        }

        void cManager_ConnectionFailed(object sender, ConnectionFailedEventArgs e)
        {
            this.ccsb.SetMessage(e.FailureReason);
            this.LoadFileLabel.Enabled = false;
            MessageBox.Show(e.FailureReason, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        void cManager_ConnectionSucceed(object sender, ConnectionSucceedEventArgs e)
        {
            this.service = e.OrganizationService;
            this.crmContext = new CrmOrganizationServiceContext(this.service);
            this.ccsb.SetConnectionStatus(true, e.ConnectionDetail);
            this.crmurl = e.ConnectionDetail.UseSsl == true ? @"https://" : @"http://";
            this.crmurl += e.ConnectionDetail.ServerName;
            this.crmurl += String.IsNullOrEmpty(e.ConnectionDetail.ServerPort) ? "" : ":" + e.ConnectionDetail.ServerPort;

           

            WhoAmIRequest request = new WhoAmIRequest();
            WhoAmIResponse response = (WhoAmIResponse)this.service.Execute(request);

            Entity currentuser = this.service.Retrieve("systemuser", response.UserId, new Microsoft.Xrm.Sdk.Query.ColumnSet("fullname"));
            this.Text += "   " + currentuser.GetAttributeValue<String>("fullname");

            Guid orgid = response.OrganizationId;
            
            Entity orga = this.service.Retrieve("organization", orgid, new ColumnSet(true));

             QueryExpression query = new QueryExpression()
                    {
                        EntityName = "role",
                        ColumnSet = new ColumnSet("roleid","name"),
                          Criteria = new FilterExpression
                {
                    Conditions = 
                         {
                            new ConditionExpression
                               {
                                   
                                  AttributeName = "name",
                                  Operator = ConditionOperator.Like,
                                  Values =  {"ExcelToCRMImport"}
                               }
                         }
                }
                    };
             EntityCollection roles = this.service.RetrieveMultiple(query);
             Boolean isauthorize = true;
             if (roles.Entities.Count() > 0)
             {
                 //The admin has block the usage of this tool.
                 isauthorize = false;
                 foreach (Entity ro in roles.Entities)
                 {
                     QueryExpression querysystemuserole = new QueryExpression()
                     {
                         EntityName = "systemuserroles",
                         ColumnSet = new ColumnSet("roleid", "systemuserid"),
                         Criteria = new FilterExpression
                         {
                             FilterOperator = LogicalOperator.And,
                             Conditions = 
                         {
                            new ConditionExpression
                               {
                                   
                                  AttributeName = "roleid",
                                  Operator = ConditionOperator.Equal,
                                  Values =  {ro.Id}
                               },
                                new ConditionExpression
                               {
                                   
                                  AttributeName = "systemuserid",
                                  Operator = ConditionOperator.EqualUserId
                               }
                         }
                         }
                     };

                     EntityCollection systemuserroles = this.service.RetrieveMultiple(querysystemuserole);
                     if (systemuserroles.Entities.Count() > 0)
                         isauthorize = true;
                 }

                 


             }

            _defaultLanguage = orga.GetAttributeValue<Int32>("localeid");// ((RetrieveAttributeResponse)crmContext.Execute(new RetrieveAttributeRequest { EntityLogicalName = "systemuser", LogicalName = "fullname" })).AttributeMetadata.DisplayName.LocalizedLabels.First().LanguageCode;
            
            lbhelp = new LabelHelper(_defaultLanguage, crmContext);
            ethelp = new EntityHelper(crmContext);
            if (isauthorize == true)
            {
                this.LoadFileLabel.Enabled = true;
            }
            else
            {
                this.LoadFileLabel.Enabled = false;
                MessageBox.Show("You haven't the permissions to use this tool, please contact your CRM Administrator", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        #endregion
        
        #region Controls Actions

        private void LoadFileLabel_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Title = "Select your excel file to import on CRM";
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.CheckPathExists = true;
            openFileDialog1.Filter = "Excel file (*.xlsx)|*.xlsx";
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;
            openFileDialog1.ReadOnlyChecked = true;
            openFileDialog1.ShowReadOnly = true;
            openFileDialog1.Multiselect = false;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Boolean fileopen = false;
                try
                {
                    using (Stream stream = new FileStream(openFileDialog1.FileName, FileMode.Open))
                    {
                    }
                    Filename = openFileDialog1.FileName;
                    fileopen = true;
                }
                catch
                {
                    MessageBox.Show("Your file is still open on another program, please close it and try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    //check here why it failed and ask user to retry if the file is in use.
                }
                if (fileopen)
                {
                    ShowWaitingBox waiting = new ShowWaitingBox();

                    try
                    {

                        WriteLog(openFileDialog1.FileName);
                        WriteLog("Analyse of your excel file, please wait. This operation can take 5 minutes.");

                        waiting.Title = "Please Wait";
                        waiting.Text = "Analyse of your excel file, please wait. This operation can take 5 minutes.";
                        waiting.Start();

                        pck = new ExcelPackage(openFileDialog1.OpenFile());

                        DebugLog("LoadHashtable");
                        LoadHashtableFromExcel(pck);
                        DebugLog("LoadDataFromExcel");
                        LoadDataFromExcel(pck);
                        DebugLog("AnalyseTabs");
                        AnalyseTabs();
                        DebugLog("End AnalyseTabs");

                        waiting.Stop();



                        AnalyseFileLabel.Enabled = true;
                        ExportToExcelLabel.Enabled = true;

                    }
                    catch (Exception ex)
                    {
                        waiting.Stop();
                        WriteLog(ex.Message);
                        WriteLog(ex.StackTrace);
                        MessageBox.Show("Unknow Error.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                        //check here why it failed and ask user to retry if the file is in use.
                    }
                }
                /*pck = new ExcelPackage(openFileDialog1.OpenFile());
                pckbackup = new ExcelPackage(openFileDialog1.OpenFile());
                pckerror = new ExcelPackage(openFileDialog1.OpenFile());
                pckresult = new ExcelPackage(openFileDialog1.OpenFile());
                AnalyseFileLabel.Enabled = true;*/

                // this.UpdateDataLabel.Enabled = false;

            }
        }

        private void OpenFileMenuItem_Click(object sender, EventArgs e)
        {
            
        }

        private void openincrmlinkStripMenuItem_Click(object sender, EventArgs e)
        {
             DataGridView CurrentdataGridView = (DataGridView)((ContextMenuStrip)(((ToolStripMenuItem)sender).Owner)).SourceControl;

             if (CurrentdataGridView != null)
             {
                 //null the first time, I need to check why
                 // Open the link in the default browser
                 if (CurrentdataGridView.SelectedCells.Count != 1)
                     MessageBox.Show("Select only 1 Cell.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information);
                 else
                 {
                     Boolean linkopened = false;
                     try
                     {
                         Uri directlinktoentity = new Uri(CurrentdataGridView.Rows[CurrentdataGridView.CurrentCell.RowIndex].Cells[CurrentdataGridView.CurrentCell.ColumnIndex].Value.ToString());
                         System.Diagnostics.Process.Start(directlinktoentity.AbsoluteUri);
                         linkopened = true;


                     }
                     catch
                     {


                     }
                     try
                     {
                         if (linkopened == false)
                         {
                             TabPage CurrentTab = (TabPage)CurrentdataGridView.Parent;

                             linkopened = OpenLinkofTheEntitie(CurrentTab.Text, CurrentdataGridView.Columns[CurrentdataGridView.CurrentCell.ColumnIndex].Name, CurrentdataGridView.Rows[CurrentdataGridView.CurrentCell.RowIndex].Cells[CurrentdataGridView.CurrentCell.ColumnIndex].Value.ToString());

                         }
                     }
                     catch
                     {


                     }
                     try
                     {
                         if (linkopened == false)
                         {
                             TabPage CurrentTab = (TabPage)CurrentdataGridView.Parent;

                             linkopened = OpenLinkofTheRelatedEntitie(CurrentTab.Text, CurrentdataGridView.Columns[CurrentdataGridView.CurrentCell.ColumnIndex].Name, CurrentdataGridView.Rows[CurrentdataGridView.CurrentCell.RowIndex].Cells[CurrentdataGridView.CurrentCell.ColumnIndex].Value.ToString());
                         }

                     }
                     catch
                     {


                     }

                     if (linkopened == false)
                         MessageBox.Show("No result for this cell. This option works only for the first column or a related object column.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information);
                 }
                 //System.Diagnostics.Process.Start(crmurl);
             }
        }

        private void dataGridView_CellRightClick(object sender, DataGridViewCellMouseEventArgs e)
        {
           
                DataGridView CurrentdataGridView = (DataGridView)(sender as Control);


                CurrentdataGridView.CurrentCell = CurrentdataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex];
            
        }

        private void dataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridView CurrentdataGridView = (DataGridView)(sender as Control);

            // Open the link in the default browser

            try
            {
                Uri directlinktoentity = new Uri(CurrentdataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
                System.Diagnostics.Process.Start(directlinktoentity.AbsoluteUri);



            }
            catch
            {


            }
            try
            {

                TabPage CurrentTab = (TabPage)CurrentdataGridView.Parent;

                OpenLinkofTheEntitie(CurrentTab.Text, CurrentdataGridView.Columns[e.ColumnIndex].Name, CurrentdataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());


            }
            catch
            {


            }
            try
            {

                TabPage CurrentTab = (TabPage)CurrentdataGridView.Parent;

                OpenLinkofTheRelatedEntitie(CurrentTab.Text, CurrentdataGridView.Columns[e.ColumnIndex].Name, CurrentdataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());


            }
            catch
            {


            }
            //System.Diagnostics.Process.Start(crmurl);

        }

        private void AnalyseFileLabel_Click(object sender, EventArgs e)
        {
            ShowWaitingBox waiting = new ShowWaitingBox();
            waiting.Title = "Please Wait";
            waiting.Text = "Analyse of your data, please wait. This operation can take 5 minutes.";
            waiting.Start();
            AnalyseTabs();
            waiting.Stop();
        }

        private void UpdateCRMDataLabel_Click(object sender, EventArgs e)
        {
            DebugLog("BackupTabs");
            BackupTabs();
            DebugLog("BackupTabs");
            DebugLog("SaveTabs");
            SaveTabstoCRM();
            DebugLog("SaveTabs");
        }

        private void AboutLabel_Click(object sender, EventArgs e)
        {

            Form About = new AboutForm();
            About.StartPosition = FormStartPosition.Manual;
            About.Location = System.Windows.Forms.Cursor.Position;
            About.Show();
        }

        private void ExportToExcelLabel_Click(object sender, EventArgs e)
        {
            ExportTabs();
        }

        private void searchToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataGridView CurrentdataGridView = (DataGridView)((ContextMenuStrip)(((ToolStripMenuItem)sender).Owner)).SourceControl;

           // DataGridViewCellMouseEventArgs ed = (DataGridViewCellMouseEventArgs)e;
            //CurrentdataGridView.CurrentCell = CurrentdataGridView.Rows[ed.RowIndex].Cells[ed.ColumnIndex];
            TabPage CurrentTab = (TabPage)CurrentdataGridView.Parent;
            DataGridViewCell CurrentCell = CurrentdataGridView.CurrentCell;
            if (CurrentdataGridView.Rows[CurrentdataGridView.CurrentCell.RowIndex].Cells[CurrentdataGridView.CurrentCell.ColumnIndex].Value != null)
            {
                String value = CurrentdataGridView.Rows[CurrentdataGridView.CurrentCell.RowIndex].Cells[CurrentdataGridView.CurrentCell.ColumnIndex].Value.ToString();

                SearchForm sf = new SearchForm(CurrentTab.Text, CurrentdataGridView, CurrentCell, value, crmContext, EntityMetadataAttributesListinExcelPackage, EntityMetadataRelationShipListinExcelPackage,
                EntityColumnsListinExcelPackage, lbhelp, ethelp, crmurl);

                sf.StartPosition = FormStartPosition.Manual;
                sf.Location = System.Windows.Forms.Cursor.Position;
                sf.Show();
            }
        }

        #endregion Controls Actions

        #region Load Data
        private void LoadHashtableFromExcel(ExcelPackage currentpackage)
        {
           

            foreach (var wsresult in currentpackage.Workbook.Worksheets)
            {
                if (EntityMetadataAttributesListinExcelPackage.ContainsKey(wsresult.Name))
                    EntityMetadataAttributesListinExcelPackage.Remove(wsresult.Name);
                if (EntityColumnsListinExcelPackage.ContainsKey(wsresult.Name))
                    EntityColumnsListinExcelPackage.Remove(wsresult.Name);
                if (EntityMetadataRelationShipListinExcelPackage.ContainsKey(wsresult.Name))
                    EntityMetadataRelationShipListinExcelPackage.Remove(wsresult.Name);


                


                 EntityMetadata currentEntity;
                 
                 if (ethelp.DoesEntityExistsbyLogicalName(wsresult.Name))
                 {
                     currentEntity = ethelp.GetEntityAttributesByLogicalName(wsresult.Name);
                 }
                 else
                 {
                     currentEntity = ethelp.GetEntityByDisplayName(wsresult.Name);
                 }

                 if (currentEntity != null)
                 {

                     EntityMetadata currentEntityRelationShip = ethelp.GetEntityRelationShipByLogicalName(currentEntity.LogicalName);
                     EntityMetadataAttributesListinExcelPackage.Add(wsresult.Name, currentEntity);
                     EntityMetadataRelationShipListinExcelPackage.Add(wsresult.Name, currentEntityRelationShip);

                     Hashtable columns = new Hashtable();

                     foreach (AttributeMetadata temp in currentEntity.Attributes)
                     {



                         try
                         {

                             if (temp != null && temp.DisplayName != null && temp.DisplayName.LocalizedLabels != null)
                             {
                                 String test = temp.DisplayName.LocalizedLabels.FirstOrDefault<LocalizedLabel>(l => l.LanguageCode == _defaultLanguage).Label;
                                 columns.Add(test, temp);


                             }
                         }
                         catch (Exception)
                         {

                         }
                     }

                     EntityColumnsListinExcelPackage.Add(wsresult.Name, columns);
                 }
             }
        }

        private void LoadDataFromExcel(ExcelPackage currentpackage)
        {
            foreach (TabPage t in this.tabControl.TabPages)
            {
                this.tabControl.TabPages.Remove(t);
            }

            foreach (var wsresult in currentpackage.Workbook.Worksheets)
            {
                TabPage tb = new TabPage(wsresult.Name);
                tb.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
            | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
                this.tabControl.TabPages.Add(tb);
                DataGridView dgw = new DataGridView();
                dgw.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
            | System.Windows.Forms.AnchorStyles.Left)
            | System.Windows.Forms.AnchorStyles.Right)));
                dgw.AllowUserToAddRows = false;
                dgw.AllowDrop = false;
                dgw.AllowUserToDeleteRows = false;
                dgw.AllowUserToOrderColumns = false;
                dgw.AllowUserToResizeColumns = true;
                dgw.AllowUserToResizeRows = true;
                dgw.EnableHeadersVisualStyles = false;
                dgw.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToDisplayedHeaders;
                dgw.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
                dgw.ReadOnly = false;
                dgw.CellMouseDown += new DataGridViewCellMouseEventHandler(dataGridView_CellRightClick);
                dgw.ContextMenuStrip = contextMenuStrip1;
                //dgw.CellContentClick += new DataGridViewCellEventHandler(dataGridView_CellContentClick);
               // dgw.CellMouseClick += new System.Windows.Forms.DataGridViewCellMouseEventHandler(dataGridView1_CellMouseClick);
                dgw.KeyDown += new System.Windows.Forms.KeyEventHandler(dataGridView1_KeyDown);
                 
              


                int clnumber = wsresult.Dimension.End.Column;
                int rownumber = wsresult.Dimension.End.Row;
                for (int c = 1; c <= clnumber; c++)
                {
                    dgw.Columns.Add(wsresult.Cells[1, c].GetValue<String>(), wsresult.Cells[1, c].GetValue<String>());
                }
                for (int r = 2; r <= rownumber; r++)
                {
                    object[] values = new object[clnumber];
                    for (int c = 1; c <= clnumber; c++)
                    {
                        try
                        {
                          
                            //To detect the date cell created by eeplus

                            if (wsresult.Cells[r, c].Style != null && wsresult.Cells[r, c].Style.Numberformat != null && wsresult.Cells[r, c].Style.Numberformat.Format != null)
                            {
                                if (wsresult.Cells[r, c].Value is Double)
                                {
                                    DateTime dt2 = DateTime.FromOADate(wsresult.Cells[r, c].GetValue<Double>());

                                    DateTime dt = wsresult.Cells[r, c].GetValue<DateTime>();

                                    String dateformattest = wsresult.Cells[r, c].Style.Numberformat.Format;

                                    if (dt2.ToString(dateformattest) == dt.ToString(dateformattest))
                                    {
                                        values[c - 1] = dt;

                                    }
                                    else
                                    {
                                        values[c - 1] = wsresult.Cells[r, c].Value;
                                    }
                                }
                                else
                                {
                                    values[c - 1] = wsresult.Cells[r, c].Value;
                                }
                            }
                            else
                            {
                                values[c - 1] = wsresult.Cells[r, c].Value;
                            }
                            



                        }
                        catch
                        {

                            values[c - 1] = wsresult.Cells[r, c].Value;
                        }
                    }
                    dgw.Rows.Add(values);
                }
                tb.Controls.Add(dgw);

                Size tempSize = dgw.Size;

                // Set the Width.
                tempSize.Width = dgw.Parent.Size.Width;
                tempSize.Height = dgw.Parent.Size.Height;
                // Set the size back.
                dgw.Size = tempSize;


            }
        }
        #endregion Load Data

        #region Analyse Data
        private void AnalyseTabs()
        {
            Boolean EnableUpdateCRM = true;
            foreach (TabPage t in this.tabControl.TabPages)
            {
                foreach (DataGridView w in t.Controls.OfType<DataGridView>())
                {
                    DebugLog("AnalyseDataGrid tab: " + t.Text);
                    if (AnalyseDataGrid(t.Text, w) == false)
                        EnableUpdateCRM = false;
                    DebugLog("End AnalyseDataGrid tab: " + t.Text);
                }
            }
            UpdateCRMDataLabel.Enabled = EnableUpdateCRM;
        }
        
        private Boolean AnalyseDataGrid(String CurrentEntitieName, DataGridView CurrentdataGridView)
        {
            Boolean isValid = true;


           
            
            //Test if the entity exist
            if(!EntityMetadataRelationShipListinExcelPackage.ContainsKey(CurrentEntitieName))
            {
                String Errormessage = "The "+CurrentEntitieName + " isn't a valid object name on CRM, modify your excel file and try again";
                WriteLog(Errormessage);
                MessageBox.Show(Errormessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
                   
            }

            EntityMetadata currentEntity = (EntityMetadata)EntityMetadataRelationShipListinExcelPackage[CurrentEntitieName];
             Hashtable columns = (Hashtable)EntityColumnsListinExcelPackage[CurrentEntitieName];
            
            //Test if the columns exist
            foreach(DataGridViewColumn column in CurrentdataGridView.Columns)
            {
                if (!columns.ContainsKey(column.Name) && (column.Name != "Direct Link to entity" && column.Name != "Result CRM Upsert"))
                {
                    column.HeaderCell.Style.BackColor = System.Drawing.Color.Red;
                    column.HeaderCell.Style.Font = new Font(CurrentdataGridView.DefaultCellStyle.Font, FontStyle.Bold);
                    String Errormessage = "No field "+ column.Name + " on the object "+CurrentEntitieName +  " in CRM, modify your excel file and try again" ;
                    column.HeaderCell.ToolTipText = Errormessage;
                    WriteLog(Errormessage);
                    isValid = false;
                }

            }


            //test if an query for the first columnvalue return more one record

            foreach (DataGridViewRow row in CurrentdataGridView.Rows)
            {
                if(CurrentdataGridView.Rows[row.Index].Cells[0].Value!= null && !String.IsNullOrEmpty(CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString()))
                {

                    String firstcolumnname = ((AttributeMetadata)columns[CurrentdataGridView.Columns[0].Name]).LogicalName;
                    List<Entity> relatedobjects = ethelp.getRelatedEntity(currentEntity, CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString(), firstcolumnname);
                       
                    //List<Entity> relatedobjects = ethelp.getRelatedObject(currentEntity, ((AttributeMetadata)columns[CurrentdataGridView.Columns[0].Name]).LogicalName, CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString());

                    if (relatedobjects != null && relatedobjects.Count > 1)
                    {
                    string Errormessage = "The value " + CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString() + " return more one records for the entity " + currentEntity.LogicalName + ". You can maybe use the id to match an unique record. ";
                    WriteLog(Errormessage);
                    isValid = false;
                    }
                }
            }
                        
            //Test if the content of the column is on the correct format
            foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
            {
                DebugLog("Analyse "+column.Name );
                if (columns.ContainsKey(column.Name))
                {
                    AttributeMetadata Attributecolumn = ethelp.getAttribute(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);

                    PicklistAttributeMetadata picklistvalues = null;
                    BooleanAttributeMetadata booleanvalues= null;
                    StatusAttributeMetadata statusvalues = null;
                    Hashtable relatedobj = new Hashtable();

                    if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist)
                        picklistvalues = ethelp.GetPicklistofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);
                    if( Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                        booleanvalues=  ethelp.GetBooleanlistofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);
                    if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                        statusvalues = ethelp.GetStatustofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);

                  

                    foreach (DataGridViewRow row in CurrentdataGridView.Rows)
                    {
                        //only if the cell contain value
                        if (CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value!= null && !String.IsNullOrEmpty(CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value.ToString()))
                        {

                            Object CurrentValueofCell = CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value;
                            DataGridViewCell CurrentCell = CurrentdataGridView.Rows[row.Index].Cells[column.Index];
                            CurrentCell.ToolTipText = null;
                            CurrentCell.Style = null;

                            //if the column is a lookup, we need to test if the value of the column return more 1 record or no record
                            if (Attributecolumn.AttributeType == AttributeTypeCode.Customer || Attributecolumn.AttributeType == AttributeTypeCode.Owner || Attributecolumn.AttributeType == AttributeTypeCode.Lookup ||  Attributecolumn.AttributeType == AttributeTypeCode.PartyList)
                            {
                                //To optimize the performance and minimize the number of request to CRM, we use an hashmap to store the relatedobjects for an value that has been already tested.
                                try
                                {
                                    List<Entity> relatedobjects = new List<Entity>();
                                    if (relatedobj.ContainsKey(CurrentValueofCell + ""))
                                    {
                                        relatedobjects = (List<Entity>)relatedobj[CurrentValueofCell + ""];
                                    }
                                    else
                                    {
                                        relatedobjects = ethelp.getRelatedObject(currentEntity, ((AttributeMetadata)columns[column.Name]).LogicalName, CurrentValueofCell + "");
                                        relatedobj.Add(CurrentValueofCell + "", relatedobjects);
                                    }

                                    if (relatedobjects.Count == 0)
                                    {
                                        string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't  a correct value for the entity " + currentEntity.LogicalName + ". No data has been find on the lookup entities. ";
                                        isValid = LogCellError(CurrentCell, Errormessage);
                                    }

                                    if (relatedobjects.Count > 1)
                                    {
                                        string Errormessage = "The value  for the Column " + column.Name + " on line " + row.Index + " return more one records for the entity " + currentEntity.LogicalName + ". You can maybe use the id to match an unique record. You can select the cell and make an right to display the possible list of values. ";
                                        isValid = LogCellError(CurrentCell, Errormessage);
                                    }
                                }
                                catch
                                {
                                    string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);
                                }
                            }


                            //if the column is a picklist, we need to test if the value corresponding to an existing value of the picklist
                            if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist )
                            {
                                try
                                {
                                    int result = lbhelp.GetValueofPicklistLabelinUserorDefaultLanguage(picklistvalues.OptionSet.Options, CurrentValueofCell + "");
                                    if (result == 0)
                                    {
                                        string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values. ";
                                        isValid = LogCellError(CurrentCell, Errormessage);
                                    }
                                }
                                catch
                                {
                                    string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values.";
                                    isValid = LogCellError(CurrentCell, Errormessage);
                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                            {
                                try
                                {
                                    int result = lbhelp.GetValueofBooleanLabelinUserorDefaultLanguage(booleanvalues, CurrentValueofCell + "");
                                    if (result == -1)
                                    {
                                        string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values. ";
                                        isValid = LogCellError(CurrentCell, Errormessage);
                                    }
                                }
                                catch
                                {
                                    string Errormessage = "The value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values.";
                                    isValid = LogCellError(CurrentCell, Errormessage);
                                }
                            }
                            //if the column is an datetime
                            if (Attributecolumn.AttributeType == AttributeTypeCode.DateTime)
                            {
                                try
                                {
                                    
                                        DateTime test = (DateTime)CurrentValueofCell;
                                    
                                }
                                catch
                                {
                                    string Errormessage = "The date format  for the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Decimal || Attributecolumn.AttributeType == AttributeTypeCode.Money)
                            {
                                try
                                {

                                    Decimal test = (Decimal)CurrentValueofCell;

                                }
                                catch
                                {
                                    string Errormessage = "The decimal format the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Double)
                            {
                                try
                                {

                                    Double test = (Double)CurrentValueofCell;

                                }
                                catch
                                {
                                    string Errormessage = "The number format the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Integer || Attributecolumn.AttributeType == AttributeTypeCode.BigInt)
                            {
                                try
                                {

                                    Int64 test = (Int64)CurrentValueofCell;

                                }
                                catch
                                {
                                    string Errormessage = "The number format the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Memo || Attributecolumn.AttributeType == AttributeTypeCode.String)
                            {
                                try
                                {

                                    String test = (String)CurrentValueofCell;

                                }
                                catch
                                {
                                    string Errormessage = "The text format the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }

                            //if the column is a picklist, we need to test if the value corresponding to an existing value of the picklist
                            if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                            {
                                try
                                {
                                    int result = lbhelp.GetValueofPicklistLabelinUserorDefaultLanguage(statusvalues.OptionSet.Options, CurrentValueofCell + "");
                                    if (result == 0)
                                    {
                                        string Errormessage = "The state value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values.";
                                        isValid = LogCellError(CurrentCell, Errormessage);
                                    }
                                }
                                catch
                                {

                                    string Errormessage = "The state value for the Column " + column.Name + " on line " + row.Index + " isn't a correct option for this field. You can select the cell and make an right to display the possible list of values.";
                                    isValid = LogCellError(CurrentCell, Errormessage);
                                }
                            }

                            if (Attributecolumn.AttributeType == AttributeTypeCode.Uniqueidentifier)
                            {
                                try
                                {

                                    Guid test = new Guid((String)CurrentValueofCell);

                                }
                                catch
                                {
                                    string Errormessage = "The number format the Column " + column.Name + " on line " + row.Index + " isn't correct. ";
                                    isValid = LogCellError(CurrentCell, Errormessage);

                                }
                            }
                        }
                    }
                }

            }

             //We test is the first column is a primarycolumn of the entity
             /*if(!columns.ContainsKey(CurrentdataGridView.Columns[0].Name))
             {
                String Errormessage = "The first column of your excelsheet "+CurrentEntitieName + " must be the primary field of the entity, modify your excel file and try again" ;
                DebugLog(Errormessage);
                DebugLog(Errormessage);
                MessageBox.Show(Errormessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;

             }
             else
             {

               
                AttributeMetadata Attributecolumn = ethelp.getAttribute(currentEntity.LogicalName,  ((AttributeMetadata)columns[CurrentdataGridView.Columns[0].Name]).LogicalName );
                if (Attributecolumn.IsPrimaryName != true)
                {
                    String Errormessage = "The first column of your excelsheet " + CurrentEntitieName + " must be the primary field of the entity, modify your excel file and try again";
                    DebugLog(Errormessage);
                    DebugLog(Errormessage);
                    MessageBox.Show(Errormessage, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
            }*/

            return isValid ;

        }
        #endregion Analyse Data

        #region backup previous data
        private void BackupTabs()
        {
            ShowWaitingBox waitingb = new ShowWaitingBox();
            waitingb.Title = "Please Wait";
            waitingb.Text = "Backup of the previous data. Please wait.";
            waitingb.Start();
            this.pckbackup = new ExcelPackage();
            
                foreach (TabPage t in this.tabControl.TabPages)
                {
                    foreach (DataGridView w in t.Controls.OfType<DataGridView>())
                    {
                        DebugLog("BackupDataGrid tab: " + t.Text);
                        ExcelWorksheet wsbackup = BackupDataGrid(t.Text, w);
                        if(wsbackup != null)
                            this.pckbackup.Workbook.Worksheets.Add(wsbackup.Name, wsbackup);
                        DebugLog("End BackupDataGrid tab: " + t.Text);
                    }
                }
            

            String backupfilepath = Filename.Replace(".xlsx", "Backup" + DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".xlsx");

            FileInfo BackupFile = new FileInfo(backupfilepath);
            FileStream fsb = BackupFile.OpenWrite();

            Byte[] infob =  this.pckbackup.GetAsByteArray();

            // Add some information to the file.
            fsb.Write(infob, 0, infob.Length);

            fsb.Close();

            waitingb.Stop();
        }

        private  ExcelWorksheet  BackupDataGrid(String CurrentEntitieName, DataGridView CurrentdataGridView)
        {
            ExcelPackage exp = new ExcelPackage();
            ExcelWorksheet ws = null;
            if (EntityMetadataRelationShipListinExcelPackage.ContainsKey(CurrentEntitieName))
            {
                

                ColumnSet allcolumnSet = new ColumnSet();

                EntityMetadata currentEntity = (EntityMetadata)EntityMetadataRelationShipListinExcelPackage[CurrentEntitieName];
                exp.Workbook.Worksheets.Add(currentEntity.LogicalName);
                ws= exp.Workbook.Worksheets[currentEntity.LogicalName];

                Hashtable columns = (Hashtable)EntityColumnsListinExcelPackage[CurrentEntitieName];
                List<Entity> entititiestobackup = new List<Entity>();
                //Test if the columns exist
                foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
                {
                    if (columns.ContainsKey(column.Name))
                    {
                       allcolumnSet.AddColumn(((AttributeMetadata)columns[column.Name]).LogicalName);
                    }
                }

                foreach (DataGridViewRow row in CurrentdataGridView.Rows)
                {
                    if (CurrentdataGridView.Rows[row.Index].Cells[0].Value != null && !String.IsNullOrEmpty(CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString()))
                    {
                       
                        String firstcolumnname = ((AttributeMetadata)columns[CurrentdataGridView.Columns[0].Name]).LogicalName;
                        List<Entity> relatedobjects = ethelp.getRelatedEntity(currentEntity, CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString(), firstcolumnname);
                        if (relatedobjects != null)
                        {
                            foreach (Entity ettobackup in relatedobjects)
                            {
                                entititiestobackup.Add(crmContext.Retrieve(currentEntity.LogicalName, ettobackup.Id, allcolumnSet));

                            }
                        }
                    }
                }

                DataTable dataTableForBackup = new DataTable();

                dataTableForBackup.Columns.Add(currentEntity.PrimaryIdAttribute, new Guid().GetType());
                Hashtable AttributeMetadataAttributecolumn = new Hashtable();
                foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
                {
                    if (columns.ContainsKey(column.Name))
                    {
                        AttributeMetadata Attributecolumn = ethelp.getAttribute(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);
                        AttributeMetadataAttributecolumn.Add(column.Name, Attributecolumn);
                        if (Attributecolumn.AttributeType == AttributeTypeCode.Customer || Attributecolumn.AttributeType == AttributeTypeCode.Owner || Attributecolumn.AttributeType == AttributeTypeCode.Lookup || Attributecolumn.AttributeType == AttributeTypeCode.PartyList)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName,new Guid().GetType());
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist || Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                        {
                            String s = "";
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, s.GetType());
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.DateTime)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, new DateTime().GetType());   
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Decimal || Attributecolumn.AttributeType == AttributeTypeCode.Money)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, new Decimal().GetType());   
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Double)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, new Double().GetType());   
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Integer || Attributecolumn.AttributeType == AttributeTypeCode.BigInt)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, new Int64().GetType());   
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Memo || Attributecolumn.AttributeType == AttributeTypeCode.String)
                        {
                            String s = "";
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, s.GetType());
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                        {
                            String s = "";
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, s.GetType());
                        }

                        if (Attributecolumn.AttributeType == AttributeTypeCode.Uniqueidentifier)
                        {
                            dataTableForBackup.Columns.Add(((AttributeMetadata)columns[column.Name]).LogicalName, new Guid().GetType());
                        }
                            
                    }
                }


                foreach (Entity et in entititiestobackup)
                {
                    object[] values = new object[dataTableForBackup.Columns.Count];
                    values[0] = et.Id;
                    int i = 0;
                    foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
                    {
                        if (columns.ContainsKey(column.Name))
                        {
                            i++;
                            AttributeMetadata Attributecolumn = (AttributeMetadata)AttributeMetadataAttributecolumn[column.Name];
                            if (!et.Contains(Attributecolumn.LogicalName))
                            {
                                values[i] = null;
                            }
                            else
                            {
                                if (Attributecolumn.AttributeType == AttributeTypeCode.Customer || Attributecolumn.AttributeType == AttributeTypeCode.Owner || Attributecolumn.AttributeType == AttributeTypeCode.Lookup || Attributecolumn.AttributeType == AttributeTypeCode.PartyList)
                                {

                                    values[i] = et.GetAttributeValue<EntityReference>(Attributecolumn.LogicalName).Id;
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist || Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                                {
                                    values[i] = lbhelp.GetValueofOptionorPicklistInDefaultlanguage(currentEntity.LogicalName, Attributecolumn.LogicalName, et.GetAttributeValue<OptionSetValue>(Attributecolumn.LogicalName).Value);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.DateTime)
                                {
                                    values[i] = et.GetAttributeValue<DateTime>(Attributecolumn.LogicalName);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Decimal || Attributecolumn.AttributeType == AttributeTypeCode.Money)
                                {
                                    values[i] = et.GetAttributeValue<Decimal>(Attributecolumn.LogicalName);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Double)
                                {
                                    values[i] = et.GetAttributeValue<Double>(Attributecolumn.LogicalName);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Integer || Attributecolumn.AttributeType == AttributeTypeCode.BigInt)
                                {
                                    values[i] = et.GetAttributeValue<Int64>(Attributecolumn.LogicalName);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Memo || Attributecolumn.AttributeType == AttributeTypeCode.String)
                                {
                                    values[i] = et.GetAttributeValue<String>(Attributecolumn.LogicalName);
                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                                {
                                    values[i] = lbhelp.GetValueofStatusInDefaultLanguage(currentEntity.LogicalName, Attributecolumn.LogicalName, et.GetAttributeValue<OptionSetValue>(Attributecolumn.LogicalName).Value);

                                }

                                if (Attributecolumn.AttributeType == AttributeTypeCode.Uniqueidentifier)
                                {
                                    values[i] = et.GetAttributeValue<Guid>(Attributecolumn.LogicalName);
                                }
                            }
                        }
                    }
                    dataTableForBackup.Rows.Add(values);
                }

                ws.Cells["A1"].LoadFromDataTable(dataTableForBackup, true);





                using (ExcelRange rng = ws.Cells[1, 1, 1, dataTableForBackup.Columns.Count])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(214, 28, 57));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);

                }


                using (ExcelRange rng = ws.Cells[1, 1, dataTableForBackup.Rows.Count + 1, dataTableForBackup.Columns.Count])
                {
                    rng.AutoFitColumns();
                }



                var dateColumns = from DataColumn d in dataTableForBackup.Columns
                                  where d.DataType == typeof(DateTime) 
                                  select d.Ordinal + 1;

                if (dataTableForBackup.Rows.Count > 0)
                {
                    foreach (var dc in dateColumns)
                    {
                        ws.Cells[2, dc, dataTableForBackup.Rows.Count + 1, dc].Style.Numberformat.Format = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern.Replace("tt", "AM/PM");// "dd/mm/yyyy h:mm";
                    }
                }

                using (ExcelRange rng = ws.Cells[1, 1, dataTableForBackup.Rows.Count + 1, dataTableForBackup.Columns.Count])
                {
                    rng.AutoFitColumns();
                    rng.AutoFilter = true;
                }

                foreach (var dc in dateColumns)
                {

                    if (Thread.CurrentThread.CurrentCulture.Name == "en-US")
                    {
                        ws.Column(dc).Width = 20;
                    }
                }
            }

            return ws;
        }
        #endregion backup previous data

        #region Save Data to CRM

        private void SaveTabstoCRM()
        {
            ShowWaitingBox waitingb = new ShowWaitingBox();
            waitingb.Title = "Please Wait";
            waitingb.Text = "Update of your data in CRM. Please wait.";
            waitingb.Start();

            Boolean erroronUpdate = false;
            foreach (TabPage t in this.tabControl.TabPages)
            {
                foreach (DataGridView w in t.Controls.OfType<DataGridView>())
                {
                    DebugLog("SaveDataGrid tab: " + t.Text);
                    if (SaveDataGridToCRM(t.Text, w) == false)
                        erroronUpdate = true;
                    DebugLog("End SaveDataGrid tab: " + t.Text);
                }
            }

            waitingb.Stop();

            DialogResult result;
            if (erroronUpdate == true)
            {

                String Message = "Some errors has been detect during the upsert operations, please check the result.";
                Message += "\nDo you want to export the result in excel?";
                 result = MessageBox.Show(Message, "Error", MessageBoxButtons.YesNo);



            }
            else
            {

                String Message = "Successfull Import.";
                Message += "\nDo you want to export the result in excel?";
                result = MessageBox.Show(Message, "Success", MessageBoxButtons.YesNo);
            }
            if (result == DialogResult.Yes)
            {
                ExportTabs();
            }
        }

        private Boolean SaveDataGridToCRM(String CurrentEntitieName, DataGridView CurrentdataGridView)
        {
            Boolean isValid = true;
            if (EntityMetadataRelationShipListinExcelPackage.ContainsKey(CurrentEntitieName))
            {

               

                EntityMetadata currentEntity = (EntityMetadata)EntityMetadataRelationShipListinExcelPackage[CurrentEntitieName];
               
                Hashtable columns = (Hashtable)EntityColumnsListinExcelPackage[CurrentEntitieName];


                PicklistAttributeMetadata picklistvalues = null;
                BooleanAttributeMetadata booleanlistvalues = null;
                StatusAttributeMetadata statusvalues = null;
                Hashtable relatedobj = new Hashtable();

               
                if(!CurrentdataGridView.Columns.Contains("Result CRM Upsert"))
                    CurrentdataGridView.Columns.Add("Result CRM Upsert", "Result CRM Upsert");
                if (!CurrentdataGridView.Columns.Contains("Direct Link to entity"))
                    CurrentdataGridView.Columns.Add("Direct Link to entity", "Direct Link to entity");

                foreach (DataGridViewRow row in CurrentdataGridView.Rows)
                {
                    Entity et = new Entity(currentEntity.LogicalName);
                    et.Id = new Guid();
                    AssignRequest assign = null;
                    String firstcolumnname = ((AttributeMetadata)columns[CurrentdataGridView.Columns[0].Name]).LogicalName;
                      
                    if (CurrentdataGridView.Rows[row.Index].Cells[0].Value != null && !String.IsNullOrEmpty(CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString()))
                    {

                         List<Entity> entitities = ethelp.getRelatedEntity(currentEntity, CurrentdataGridView.Rows[row.Index].Cells[0].Value.ToString(), firstcolumnname);
                        if (entitities != null)
                        {
                            et.Id = entitities.First().Id;
                        }
                    }

                        foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
                        {
                            if (columns.ContainsKey(column.Name))
                            {
                                AttributeMetadata Attributecolumn = ethelp.getAttribute(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);

                                if ((et.Id != new Guid() && Attributecolumn.IsValidForUpdate == true) || (et.Id == new Guid() && Attributecolumn.IsValidForCreate == true ))
                                {
                                    if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist )
                                        picklistvalues = ethelp.GetPicklistofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);
                                    if ( Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                                        booleanlistvalues = ethelp.GetBooleanlistofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);
                                    if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                                        statusvalues = ethelp.GetStatustofField(currentEntity.LogicalName, ((AttributeMetadata)columns[column.Name]).LogicalName);


                                    if (CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value != null && !String.IsNullOrEmpty(CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value.ToString()))
                                    {

                                        Object CurrentValueofCell = CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value;


                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Owner)
                                        {
                                            List<Entity> relatedobjects = new List<Entity>();
                                            if (relatedobj.ContainsKey((String)CurrentValueofCell))
                                            {
                                                relatedobjects = (List<Entity>)relatedobj[(String)CurrentValueofCell];
                                            }
                                            else
                                            {
                                                List<Entity> ownerlist = new List<Entity>();

                                                relatedobjects = ethelp.getRelatedObject(currentEntity, ((AttributeMetadata)columns[column.Name]).LogicalName, (String)CurrentValueofCell);
                                                if (relatedobjects.Count > 0)
                                                {
                                                    foreach (Entity eto in relatedobjects)
                                                    {
                                                        Entity owner = crmContext.Retrieve(eto.GetAttributeValue<String>("owneridtype"), eto.Id, new ColumnSet(true));
                                                        ownerlist.Add(owner);
                                                    }
                                                }

                                                relatedobjects = ownerlist;
                                                relatedobj.Add((String)CurrentValueofCell, relatedobjects);
                                            }
                                            if (et.Id == new Guid())
                                            {
                                                if (relatedobjects.Count >= 1)
                                                {
                                                    et.SetAttributeValue(Attributecolumn.LogicalName, new EntityReference(relatedobjects.First().LogicalName, relatedobjects.First().Id));
                                                }
                                            }
                                            else
                                            {
                                                assign = new AssignRequest

                                                {
                                                    Assignee = new EntityReference(relatedobjects.First().LogicalName, relatedobjects.First().Id),
                                                    Target = new EntityReference(currentEntity.LogicalName, et.Id)

                                                };
                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Customer || Attributecolumn.AttributeType == AttributeTypeCode.Lookup || Attributecolumn.AttributeType == AttributeTypeCode.PartyList)
                                        {
                                            //To optimize the performance and minimize the number of request to CRM, we use an hashmap to store the relatedobjects for an value that has been already tested.

                                            List<Entity> relatedobjects = new List<Entity>();
                                            if (relatedobj.ContainsKey((String)CurrentValueofCell))
                                            {
                                                relatedobjects = (List<Entity>)relatedobj[(String)CurrentValueofCell];
                                            }
                                            else
                                            {
                                                relatedobjects = ethelp.getRelatedObject(currentEntity, ((AttributeMetadata)columns[column.Name]).LogicalName, (String)CurrentValueofCell);
                                                relatedobj.Add((String)CurrentValueofCell, relatedobjects);
                                            }



                                            if (relatedobjects.Count >= 1)
                                            {
                                                et.SetAttributeValue(Attributecolumn.LogicalName, new EntityReference(relatedobjects.First().LogicalName, relatedobjects.First().Id));
                                            }
                                        }


                                        //if the column is a picklist, we need to test if the value corresponding to an existing value of the picklist
                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Picklist )
                                        {
                                            int result = lbhelp.GetValueofPicklistLabelinUserorDefaultLanguage(picklistvalues.OptionSet.Options, (String)CurrentValueofCell);
                                            if (result > 0)
                                            {
                                                et.SetAttributeValue(Attributecolumn.LogicalName, new OptionSetValue(result));
                                            }
                                        }

                                        //if the column is a picklist, we need to test if the value corresponding to an existing value of the picklist
                                        if ( Attributecolumn.AttributeType == AttributeTypeCode.Boolean)
                                        {
                                            int result = lbhelp.GetValueofBooleanLabelinUserorDefaultLanguage(booleanlistvalues, (String)CurrentValueofCell);
                                            if (result > -1)
                                            {
                                                et.SetAttributeValue(Attributecolumn.LogicalName, result == 1? true :false);
                                            }
                                        }

                                        //if the column is an datetime
                                        if (Attributecolumn.AttributeType == AttributeTypeCode.DateTime)
                                        {
                                            try
                                            {

                                                DateTime test = (DateTime)CurrentValueofCell;
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);

                                            }
                                            catch
                                            {


                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Decimal || Attributecolumn.AttributeType == AttributeTypeCode.Money)
                                        {
                                            try
                                            {

                                                Decimal test = (Decimal)CurrentValueofCell;
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);

                                            }
                                            catch
                                            {


                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Double)
                                        {
                                            try
                                            {

                                                Double test = (Double)CurrentValueofCell;
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);

                                            }
                                            catch
                                            {


                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Integer || Attributecolumn.AttributeType == AttributeTypeCode.BigInt)
                                        {
                                            try
                                            {

                                                Int64 test = (Int64)CurrentValueofCell;
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);
                                            }
                                            catch
                                            {


                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Memo || Attributecolumn.AttributeType == AttributeTypeCode.String)
                                        {
                                            try
                                            {

                                                String test = (String)CurrentValueofCell;
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);

                                            }
                                            catch
                                            {


                                            }
                                        }

                                        //if the column is a picklist, we need to test if the value corresponding to an existing value of the picklist
                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Status)
                                        {
                                            int result = lbhelp.GetValueofPicklistLabelinUserorDefaultLanguage(statusvalues.OptionSet.Options, (String)CurrentValueofCell);
                                            if (result > 0)
                                            {
                                                et.SetAttributeValue(Attributecolumn.LogicalName, new OptionSetValue(result));
                                            }
                                        }

                                        if (Attributecolumn.AttributeType == AttributeTypeCode.Uniqueidentifier)
                                        {
                                            try
                                            {

                                                Guid test = new Guid((String)CurrentValueofCell);
                                                et.SetAttributeValue(Attributecolumn.LogicalName, test);

                                            }
                                            catch
                                            {


                                            }
                                        }
                                    }


                                }
                            }
                        }

                        Guid updateID = new Guid();
                        try
                        {
                            crmContext.Detach(et);
                            if (et.Id == new Guid())
                            {

                                updateID = crmContext.Create(et);

                            }
                            else
                            {
                                crmContext.Update(et);
                                updateID = et.Id;
                            }
                            crmContext.SaveChanges();

                            if (assign != null)
                            {
                                // Execute the Request
                                crmContext.Execute(assign);
                                
                               
                            }
                            Uri directlink = new Uri(crmurl + "/main.aspx?etn=" + currentEntity.LogicalName + "&id=%7b" + updateID.ToString() + "%7d&rof=false&pagetype=entityrecord");
                            CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Result CRM Upsert"].Index].Value = "Success";
                            SuccessCellMessage(CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Result CRM Upsert"].Index], null);
                            CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Direct Link to entity"].Index].Value = directlink;
                            ColumnSet firstcolumn = new ColumnSet(firstcolumnname);
                            Entity newent = crmContext.Retrieve(currentEntity.LogicalName, updateID, firstcolumn);
                            if (newent.Contains(firstcolumnname))
                            {
                                CurrentdataGridView.Rows[row.Index].Cells[0].Value = newent.GetAttributeValue(firstcolumnname);
                                
                            }
                        }
                        catch (Exception ex)
                        {
                            CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Result CRM Upsert"].Index].Value = "Error";
                            CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Direct Link to entity"].Index].Value = ex.Message;
                            LogCellError(CurrentdataGridView.Rows[row.Index].Cells[CurrentdataGridView.Columns["Result CRM Upsert"].Index], ex.Message);
                            
                            DebugLog(ex.Source);
                            DebugLog(ex.StackTrace);
                            isValid = false;
                        }
                    






                }
            }

            return isValid;
        }


        #endregion Save Data to CRM

        #region LogFunction
        private Boolean SuccessCellMessage(DataGridViewCell CurrentCell, String SuccessMessage)
        {
            if(!String.IsNullOrEmpty(SuccessMessage))
                CurrentCell.ToolTipText = SuccessMessage;
            CurrentCell.Style.BackColor = System.Drawing.Color.Green;
            return true;
        }
        private Boolean LogCellError(DataGridViewCell CurrentCell, String ErrorMessage)
        {
            WriteLog(ErrorMessage);
            CurrentCell.Style.BackColor = System.Drawing.Color.Red;
            CurrentCell.ToolTipText = ErrorMessage;
            return false;
        }

        private void DebugLog(string log)
        {
            log = DateTime.Now.ToString() + " " + log;
            if (!String.IsNullOrEmpty(this.displayDebuglog) && this.displayDebuglog.ToLower() == "true")
            {
                ConsoleBox.AppendText(" Debug " + log + "\n");
            }



            if (!String.IsNullOrEmpty(this.filelog))
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(this.filelog, true))
                {
                    file.WriteLine(log);
                }

            }

        }

        private void WriteLog(string log)
        {
            log = DateTime.Now.ToString() + " " + log;

            ConsoleBox.AppendText(log + "\n");




            if (!String.IsNullOrEmpty(this.filelog))
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(this.filelog, true))
                {
                    file.WriteLine(log);
                }

            }

        }

        #endregion LogFunction

        #region Other Functions

        private Boolean OpenLinkofTheRelatedEntitie(String CurrentEntitieName, String ColumnName, String CellValue)
        {

            if (EntityMetadataRelationShipListinExcelPackage.ContainsKey(CurrentEntitieName) && EntityColumnsListinExcelPackage.ContainsKey(CurrentEntitieName))
            {
                Hashtable columns = (Hashtable)EntityColumnsListinExcelPackage[CurrentEntitieName];
                EntityMetadata currentEntity = (EntityMetadata)EntityMetadataRelationShipListinExcelPackage[CurrentEntitieName];

                if (columns.ContainsKey(ColumnName))
                {
                    

                    AttributeMetadata CurrentEntityField = (AttributeMetadata)columns[ColumnName];
                    if (CurrentEntityField.AttributeType == AttributeTypeCode.Customer || CurrentEntityField.AttributeType == AttributeTypeCode.Owner || CurrentEntityField.AttributeType == AttributeTypeCode.Lookup)
                    {
                        List<Entity> relatedobjects = ethelp.getRelatedObject(currentEntity, ((AttributeMetadata)columns[ColumnName]).LogicalName, CellValue);
                        if (relatedobjects.Count == 1)
                        {
                            Uri directlinktoentity = new Uri(crmurl + "/main.aspx?etn=" + relatedobjects.First().LogicalName + "&id=%7b" + relatedobjects.First().Id.ToString() + "%7d&rof=false&pagetype=entityrecord");
                            System.Diagnostics.Process.Start(directlinktoentity.AbsoluteUri);
                            return true;
                        }
                    }
                }
            }
            return false;
        }

        private Boolean OpenLinkofTheEntitie(String CurrentEntitieName, String ColumnName, String CellValue)
        {

            if (EntityMetadataRelationShipListinExcelPackage.ContainsKey(CurrentEntitieName) && EntityColumnsListinExcelPackage.ContainsKey(CurrentEntitieName))
            {
                Hashtable columns = (Hashtable)EntityColumnsListinExcelPackage[CurrentEntitieName];
                EntityMetadata currentEntity = (EntityMetadata)EntityMetadataRelationShipListinExcelPackage[CurrentEntitieName];

                if (columns.ContainsKey(ColumnName))
                {


                    AttributeMetadata CurrentEntityField = (AttributeMetadata)columns[ColumnName];
                    List<Entity> relatedobjects = ethelp.getRelatedEntity(currentEntity, CellValue, ((AttributeMetadata)columns[ColumnName]).LogicalName);
                        if (relatedobjects.Count == 1)
                        {
                            Uri directlinktoentity = new Uri(crmurl + "/main.aspx?etn=" + relatedobjects.First().LogicalName + "&id=%7b" + relatedobjects.First().Id.ToString() + "%7d&rof=false&pagetype=entityrecord");
                            System.Diagnostics.Process.Start(directlinktoentity.AbsoluteUri);
                            return true;
                        }
                    
                }
            }
            return false;
        }
        #endregion Other Functions

        #region copy paste

      /*  private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {

                DataGridView dataGridView1 = (DataGridView)(sender as Control);
                if (dataGridView1.SelectedCells.Count > 0)
                {

                    dataGridView1.ContextMenuStrip = contextMenuStrip1;
                   
                        
                    
                    dataGridView1.ContextMenuStrip.Show(MousePosition);
                   

                }

            }
        }*/

        private void cutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataGridView dataGridView1 = (DataGridView)((ContextMenuStrip)(((ToolStripMenuItem)sender).Owner)).SourceControl;
           
            //Copy to clipboard
            CopyToClipboard(dataGridView1);

            //Clear selected cells
            foreach (DataGridViewCell dgvCell in dataGridView1.SelectedCells)
                dgvCell.Value = string.Empty;
        }

        private void copyToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataGridView dataGridView1 = (DataGridView)((ContextMenuStrip)(((ToolStripMenuItem)sender).Owner)).SourceControl;
           
     
            CopyToClipboard(dataGridView1);
        }

        private void pasteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Perform paste Operation
            DataGridView dataGridView1 = (DataGridView)((ContextMenuStrip)(((ToolStripMenuItem)sender).Owner)).SourceControl;
           
            PasteClipboardValue(dataGridView1);
        }

        private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
        {
            DataGridView dataGridView1 = (DataGridView)(sender as Control);
            try
            {
                if (e.Modifiers == Keys.Control)
                {
                    switch (e.KeyCode)
                    {
                        case Keys.C:
                            CopyToClipboard(dataGridView1);
                            break;

                        case Keys.V:
                            PasteClipboardValue(dataGridView1);
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Copy/paste operation failed. " + ex.Message, "Copy/Paste", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void CopyToClipboard(DataGridView dataGridView1)
        {
            //Copy to clipboard
            DataObject dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }

        private void PasteClipboardValue(DataGridView dataGridView1)
        {
            //Show Error if no cell is selected
            if (dataGridView1.SelectedCells.Count == 0)
            {
                MessageBox.Show("Please select a cell", "Paste",
                MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //Get the starting Cell
            DataGridViewCell startCell = GetStartCell(dataGridView1);
            //Get the clipboard value in a dictionary
            Dictionary<int, Dictionary<int, string>> cbValue =
                    ClipBoardValues(Clipboard.GetText());

            int iRowIndex = startCell.RowIndex;
            foreach (int rowKey in cbValue.Keys)
            {
                int iColIndex = startCell.ColumnIndex;
                foreach (int cellKey in cbValue[rowKey].Keys)
                {
                    //Check if the index is within the limit
                    if (iColIndex <= dataGridView1.Columns.Count - 1
                    && iRowIndex <= dataGridView1.Rows.Count - 1)
                    {
                        DataGridViewCell cell = dataGridView1[iColIndex, iRowIndex];

                        //Copy to selected cells if 'chkPasteToSelectedCells' is checked
                        if (cell.Selected)
                            cell.Value = cbValue[rowKey][cellKey];
                    }
                    iColIndex++;
                }
                iRowIndex++;
            }
        }

        private DataGridViewCell GetStartCell(DataGridView dgView)
        {
            //get the smallest row,column index
            if (dgView.SelectedCells.Count == 0)
                return null;

            int rowIndex = dgView.Rows.Count - 1;
            int colIndex = dgView.Columns.Count - 1;

            foreach (DataGridViewCell dgvCell in dgView.SelectedCells)
            {
                if (dgvCell.RowIndex < rowIndex)
                    rowIndex = dgvCell.RowIndex;
                if (dgvCell.ColumnIndex < colIndex)
                    colIndex = dgvCell.ColumnIndex;
            }

            return dgView[colIndex, rowIndex];
        }

        private Dictionary<int, Dictionary<int, string>> ClipBoardValues(string clipboardValue)
        {
            Dictionary<int, Dictionary<int, string>>
            copyValues = new Dictionary<int, Dictionary<int, string>>();

            String[] lines = clipboardValue.Split('\n');

            for (int i = 0; i <= lines.Length - 1; i++)
            {
                copyValues[i] = new Dictionary<int, string>();
                String[] lineContent = lines[i].Split('\t');

                //if an empty cell value copied, then set the dictionary with an empty string
                //else Set value to dictionary
                if (lineContent.Length == 0)
                    copyValues[i][0] = string.Empty;
                else
                {
                    for (int j = 0; j <= lineContent.Length - 1; j++)
                        copyValues[i][j] = lineContent[j];
                }
            }
            return copyValues;
        }
        #endregion copy paste

        #region export To Excel

        private void ExportTabs()
        {


            SaveFileDialog SaveFileXls = new SaveFileDialog();
            SaveFileXls.Title = "Enter a file name to save your data";
            SaveFileXls.CheckFileExists = false;
            SaveFileXls.CheckPathExists = true;
            SaveFileXls.Filter = "Excel file (*.xlsx)|*.xlsx";
            SaveFileXls.FilterIndex = 1;
            SaveFileXls.RestoreDirectory = true;
            

            if (SaveFileXls.ShowDialog() == DialogResult.OK)
            {



            ShowWaitingBox waitingb = new ShowWaitingBox();
            waitingb.Title = "Please Wait";
            waitingb.Text = "Export to Excel. Please wait.";
            waitingb.Start();
            this.pckbackup = new ExcelPackage();

            foreach (TabPage t in this.tabControl.TabPages)
            {
                foreach (DataGridView w in t.Controls.OfType<DataGridView>())
                {
                    DebugLog("BackupDataGrid tab: " + t.Text);
                    ExcelWorksheet wsbackup = ExportDataGrid(t.Text, w);
                    if (wsbackup != null)
                        this.pckbackup.Workbook.Worksheets.Add(wsbackup.Name, wsbackup);
                    DebugLog("End BackupDataGrid tab: " + t.Text);
                }
            }


            String backupfilepath = SaveFileXls.FileName;

            FileInfo BackupFile = new FileInfo(backupfilepath);
            FileStream fsb = BackupFile.OpenWrite();

            Byte[] infob = this.pckbackup.GetAsByteArray();

            // Add some information to the file.
            fsb.Write(infob, 0, infob.Length);

            fsb.Close();

            waitingb.Stop();
            }
        }

        private ExcelWorksheet ExportDataGrid(String CurrentEntitieName, DataGridView CurrentdataGridView)
        {
            ExcelPackage exp = new ExcelPackage();
            ExcelWorksheet ws = null;

            if (CurrentdataGridView.Rows.Count > 0)
            {
                exp.Workbook.Worksheets.Add(CurrentEntitieName);
                ws = exp.Workbook.Worksheets[CurrentEntitieName];

                DataTable dataTableForBackup = new DataTable();

                foreach (DataGridViewColumn c in CurrentdataGridView.Columns)
                {
                    dataTableForBackup.Columns.Add(c.Name, CurrentdataGridView.Rows[0].Cells[c.Index].Value.GetType());
                }


                object[] values = new object[dataTableForBackup.Columns.Count];

                foreach (DataGridViewRow row in CurrentdataGridView.Rows)
                {
                    foreach (DataGridViewColumn column in CurrentdataGridView.Columns)
                    {

                        values[column.Index] = CurrentdataGridView.Rows[row.Index].Cells[column.Index].Value;

                    }
                    dataTableForBackup.Rows.Add(values);
                }




                ws.Cells["A1"].LoadFromDataTable(dataTableForBackup, true);





                using (ExcelRange rng = ws.Cells[1, 1, 1, dataTableForBackup.Columns.Count])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(214, 28, 57));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);

                }


                using (ExcelRange rng = ws.Cells[1, 1, dataTableForBackup.Rows.Count + 1, dataTableForBackup.Columns.Count])
                {
                    rng.AutoFitColumns();
                }



                var dateColumns = from DataColumn d in dataTableForBackup.Columns
                                  where d.DataType == typeof(DateTime)
                                  select d.Ordinal + 1;

                if (dataTableForBackup.Rows.Count > 0)
                {
                    foreach (var dc in dateColumns)
                    {
                        ws.Cells[2, dc, dataTableForBackup.Rows.Count + 1, dc].Style.Numberformat.Format = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern.Replace("tt", "AM/PM");// "dd/mm/yyyy h:mm";
                    }
                }

                using (ExcelRange rng = ws.Cells[1, 1, dataTableForBackup.Rows.Count + 1, dataTableForBackup.Columns.Count])
                {
                    rng.AutoFitColumns();
                    rng.AutoFilter = true;
                }

                foreach (var dc in dateColumns)
                {

                    if (Thread.CurrentThread.CurrentCulture.Name == "en-US")
                    {
                        ws.Column(dc).Width = 20;
                    }
                }

            }
            return ws;

        }
        #endregion export To Excel

      

        

        







    }
}
